-- For expression IS [NOT] NAN|INFINITE

-- create new schema
drop schema if exists expr_nan;
create schema expr_nan;
set search_path=expr_nan;

CREATE TABLE tnf (c1 int, c2 bigint, c3 float8, c4 varchar(50), c5 text, c6 timestamp);
INSERT INTO tnf VALUES (1, 10000, 100.008, '1234.5679', '987654321', TIMESTAMP'2024-06-06 21:03:58');
INSERT INTO tnf VALUES (2, -922337203, -1.79E+100, '-1.79E+100', '1.79E+100', TIMESTAMP'1970-01-01 00:00:00');
INSERT INTO tnf VALUES (3, 922337203, 1.79E+100, '9,223,372,036,854,775,807', '1.79E+400',  TIMESTAMP'2077-07-08 00:00:00');
INSERT INTO tnf VALUES (4, NULL, NULL, NULL, NULL, NULL);
INSERT INTO tnf VALUES (5, -0,  CAST('NaN' as float8), 'tonight', '12.34yesterday', 'today');
INSERT INTO tnf VALUES (6, 3.14E+3, CAST('Inf' as float8), 'NaN', 'Inf', 'now');

SELECT c1, c2 FROM tnf WHERE c1 = 1 AND c2 IS NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 1 AND c2 IS NOT NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 1 AND c2 IS INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 1 AND c2 IS NOT INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 2 AND c2 IS NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 2 AND c2 IS NOT NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 2 AND c2 IS INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 2 AND c2 IS NOT INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 3 AND c2 IS NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 3 AND c2 IS NOT NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 3 AND c2 IS INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 3 AND c2 IS NOT INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 4 AND c2 IS NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 4 AND c2 IS NOT NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 4 AND c2 IS INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 4 AND c2 IS NOT INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 5 AND c2 IS NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 5 AND c2 IS NOT NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 5 AND c2 IS INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 5 AND c2 IS NOT INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 6 AND c2 IS NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 6 AND c2 IS NOT NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 6 AND c2 IS INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 6 AND c2 IS NOT INFINITE;

SELECT c1, c3 FROM tnf WHERE c1 = 1 AND c3 IS NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 1 AND c3 IS NOT NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 1 AND c3 IS INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 1 AND c3 IS NOT INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 2 AND c3 IS NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 2 AND c3 IS NOT NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 2 AND c3 IS INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 2 AND c3 IS NOT INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 3 AND c3 IS NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 3 AND c3 IS NOT NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 3 AND c3 IS INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 3 AND c3 IS NOT INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 4 AND c3 IS NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 4 AND c3 IS NOT NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 4 AND c3 IS INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 4 AND c3 IS NOT INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 5 AND c3 IS NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 5 AND c3 IS NOT NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 5 AND c3 IS INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 5 AND c3 IS NOT INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 6 AND c3 IS NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 6 AND c3 IS NOT NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 6 AND c3 IS INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 6 AND c3 IS NOT INFINITE;

SELECT c1, c4 FROM tnf WHERE c1 = 1 AND c4 IS NAN;
SELECT c1, c4 FROM tnf WHERE c1 = 1 AND c4 IS NOT NAN;
SELECT c1, c4 FROM tnf WHERE c1 = 1 AND c4 IS INFINITE;
SELECT c1, c4 FROM tnf WHERE c1 = 1 AND c4 IS NOT INFINITE;
SELECT c1, c4 FROM tnf WHERE c1 = 2 AND c4 IS NAN;
SELECT c1, c4 FROM tnf WHERE c1 = 2 AND c4 IS NOT NAN;
SELECT c1, c4 FROM tnf WHERE c1 = 2 AND c4 IS INFINITE;
SELECT c1, c4 FROM tnf WHERE c1 = 2 AND c4 IS NOT INFINITE;
SELECT c1, c4 FROM tnf WHERE c1 = 3 AND c4 IS NAN;  -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 3 AND c4 IS NOT NAN;  -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 3 AND c4 IS INFINITE;  -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 3 AND c4 IS NOT INFINITE;  -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 4 AND c4 IS NAN;
SELECT c1, c4 FROM tnf WHERE c1 = 4 AND c4 IS NOT NAN;
SELECT c1, c4 FROM tnf WHERE c1 = 4 AND c4 IS INFINITE;
SELECT c1, c4 FROM tnf WHERE c1 = 4 AND c4 IS NOT INFINITE;
SELECT c1, c4 FROM tnf WHERE c1 = 5 AND c4 IS NAN; -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 5 AND c4 IS NOT NAN; -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 5 AND c4 IS INFINITE; -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 5 AND c4 IS NOT INFINITE; -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 6 AND c4 IS NAN; -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 6 AND c4 IS NOT NAN; -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 6 AND c4 IS INFINITE; -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 6 AND c4 IS NOT INFINITE; -- error: invalid number

SELECT c1, c5 FROM tnf WHERE c1 = 1 AND c5 IS NAN;
SELECT c1, c5 FROM tnf WHERE c1 = 1 AND c5 IS NOT NAN;
SELECT c1, c5 FROM tnf WHERE c1 = 1 AND c5 IS INFINITE;
SELECT c1, c5 FROM tnf WHERE c1 = 1 AND c5 IS NOT INFINITE;
SELECT c1, c5 FROM tnf WHERE c1 = 2 AND c5 IS NAN;
SELECT c1, c5 FROM tnf WHERE c1 = 2 AND c5 IS NOT NAN;
SELECT c1, c5 FROM tnf WHERE c1 = 2 AND c5 IS INFINITE;
SELECT c1, c5 FROM tnf WHERE c1 = 2 AND c5 IS NOT INFINITE;
SELECT c1, c5 FROM tnf WHERE c1 = 3 AND c5 IS NAN;  -- error: overflow
SELECT c1, c5 FROM tnf WHERE c1 = 3 AND c5 IS NOT NAN;  -- error: overflow
SELECT c1, c5 FROM tnf WHERE c1 = 3 AND c5 IS INFINITE;  -- error: overflow
SELECT c1, c5 FROM tnf WHERE c1 = 3 AND c5 IS NOT INFINITE;  -- error: overflow
SELECT c1, c5 FROM tnf WHERE c1 = 4 AND c5 IS NAN;
SELECT c1, c5 FROM tnf WHERE c1 = 4 AND c5 IS NOT NAN;
SELECT c1, c5 FROM tnf WHERE c1 = 4 AND c5 IS INFINITE;
SELECT c1, c5 FROM tnf WHERE c1 = 4 AND c5 IS NOT INFINITE;
SELECT c1, c5 FROM tnf WHERE c1 = 5 AND c5 IS NAN; -- error: invalid number
SELECT c1, c5 FROM tnf WHERE c1 = 5 AND c5 IS NOT NAN; -- error: invalid number
SELECT c1, c5 FROM tnf WHERE c1 = 5 AND c5 IS INFINITE; -- error: invalid number
SELECT c1, c5 FROM tnf WHERE c1 = 5 AND c5 IS NOT INFINITE; -- error: invalid number
SELECT c1, c5 FROM tnf WHERE c1 = 6 AND c5 IS NAN; -- error: invalid number
SELECT c1, c5 FROM tnf WHERE c1 = 6 AND c5 IS NOT NAN; -- error: invalid number
SELECT c1, c5 FROM tnf WHERE c1 = 6 AND c5 IS INFINITE; -- error: invalid number
SELECT c1, c5 FROM tnf WHERE c1 = 6 AND c5 IS NOT INFINITE; -- error: invalid number

-- all error: can't cast timestamp to double precision
SELECT c1, c6 FROM tnf WHERE c1 = 1 AND c6 IS NAN; 
SELECT c1, c6 FROM tnf WHERE c1 = 1 AND c6 IS NOT NAN;
SELECT c1, c6 FROM tnf WHERE c1 = 1 AND c6 IS INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 1 AND c6 IS NOT INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 2 AND c6 IS NAN;
SELECT c1, c6 FROM tnf WHERE c1 = 2 AND c6 IS NOT NAN;
SELECT c1, c6 FROM tnf WHERE c1 = 2 AND c6 IS INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 2 AND c6 IS NOT INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 3 AND c6 IS NAN; 
SELECT c1, c6 FROM tnf WHERE c1 = 3 AND c6 IS NOT NAN;
SELECT c1, c6 FROM tnf WHERE c1 = 3 AND c6 IS INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 3 AND c6 IS NOT INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 4 AND c6 IS NAN;
SELECT c1, c6 FROM tnf WHERE c1 = 4 AND c6 IS NOT NAN;
SELECT c1, c6 FROM tnf WHERE c1 = 4 AND c6 IS INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 4 AND c6 IS NOT INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 5 AND c6 IS NAN;
SELECT c1, c6 FROM tnf WHERE c1 = 5 AND c6 IS NOT NAN;
SELECT c1, c6 FROM tnf WHERE c1 = 5 AND c6 IS INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 5 AND c6 IS NOT INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 6 AND c6 IS NAN;
SELECT c1, c6 FROM tnf WHERE c1 = 6 AND c6 IS NOT NAN;
SELECT c1, c6 FROM tnf WHERE c1 = 6 AND c6 IS INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 6 AND c6 IS NOT INFINITE;

SELECT c2 IS NAN, c2 IS NOT NAN, c2 IS INFINITE, c2 IS NOT INFINITE FROM tnf ORDER BY c1;
SELECT c3 IS NAN, c3 IS NOT NAN, c3 IS INFINITE, c3 IS NOT INFINITE FROM tnf ORDER BY c1;
SELECT c4 IS NAN, c4 IS NOT NAN, c4 IS INFINITE, c4 IS NOT INFINITE FROM tnf ORDER BY c1;
SELECT c5 IS NAN, c5 IS NOT NAN, c5 IS INFINITE, c5 IS NOT INFINITE FROM tnf ORDER BY c1;
SELECT c6 IS NAN, c6 IS NOT NAN, c6 IS INFINITE, c6 IS NOT INFINITE FROM tnf ORDER BY c1;

-- not
SELECT c1, c3, NOT(c3 IS NOT NAN), NOT(c3 IS NAN) FROM tnf ORDER BY c1;
SELECT c1, c3, NOT(c3 IS NOT INFINITE), NOT(c3 IS INFINITE) FROM tnf ORDER BY c1;

-- collation
SELECT c1, c2, c3, c4, c5 FROM tnf ORDER BY (c3 IS NAN);

SELECT (1 + 1) IS NAN;
SELECT (c3 + 1) IS NAN FROM tnf;
SELECT c1, c2, c3, c4, c5 FROM tnf WHERE (c3 + 1) IS NOT NAN;
SELECT c1, c2, c3, c4, c5 FROM tnf WHERE (c4 + 1) IS NOT NAN; -- error

SELECT (SELECT c3 FROM tnf WHERE c1 = 3) IS NAN;
SELECT c1, c2, c3, c4, c5 FROM tnf WHERE (SELECT c3 FROM tnf WHERE c1 = 5) IS nan;

SELECT c1, c2, c3, c4, c5 FROM tnf WHERE c1 IN (1, 2, 4, 5) AND c3 IS NAN;
SELECT c1, c2, c3, c4, c5 FROM tnf WHERE c1 IN (1, 2) AND c4 IS NAN;

-- test enable_expr_fusion
set enable_expr_fusion=on;

SELECT c1, c2 FROM tnf WHERE c1 = 1 AND c2 IS NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 1 AND c2 IS NOT NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 1 AND c2 IS INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 1 AND c2 IS NOT INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 2 AND c2 IS NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 2 AND c2 IS NOT NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 2 AND c2 IS INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 2 AND c2 IS NOT INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 3 AND c2 IS NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 3 AND c2 IS NOT NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 3 AND c2 IS INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 3 AND c2 IS NOT INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 4 AND c2 IS NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 4 AND c2 IS NOT NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 4 AND c2 IS INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 4 AND c2 IS NOT INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 5 AND c2 IS NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 5 AND c2 IS NOT NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 5 AND c2 IS INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 5 AND c2 IS NOT INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 6 AND c2 IS NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 6 AND c2 IS NOT NAN;
SELECT c1, c2 FROM tnf WHERE c1 = 6 AND c2 IS INFINITE;
SELECT c1, c2 FROM tnf WHERE c1 = 6 AND c2 IS NOT INFINITE;

SELECT c1, c3 FROM tnf WHERE c1 = 1 AND c3 IS NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 1 AND c3 IS NOT NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 1 AND c3 IS INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 1 AND c3 IS NOT INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 2 AND c3 IS NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 2 AND c3 IS NOT NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 2 AND c3 IS INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 2 AND c3 IS NOT INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 3 AND c3 IS NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 3 AND c3 IS NOT NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 3 AND c3 IS INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 3 AND c3 IS NOT INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 4 AND c3 IS NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 4 AND c3 IS NOT NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 4 AND c3 IS INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 4 AND c3 IS NOT INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 5 AND c3 IS NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 5 AND c3 IS NOT NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 5 AND c3 IS INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 5 AND c3 IS NOT INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 6 AND c3 IS NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 6 AND c3 IS NOT NAN;
SELECT c1, c3 FROM tnf WHERE c1 = 6 AND c3 IS INFINITE;
SELECT c1, c3 FROM tnf WHERE c1 = 6 AND c3 IS NOT INFINITE;

SELECT c1, c4 FROM tnf WHERE c1 = 1 AND c4 IS NAN;
SELECT c1, c4 FROM tnf WHERE c1 = 1 AND c4 IS NOT NAN;
SELECT c1, c4 FROM tnf WHERE c1 = 1 AND c4 IS INFINITE;
SELECT c1, c4 FROM tnf WHERE c1 = 1 AND c4 IS NOT INFINITE;
SELECT c1, c4 FROM tnf WHERE c1 = 2 AND c4 IS NAN;
SELECT c1, c4 FROM tnf WHERE c1 = 2 AND c4 IS NOT NAN;
SELECT c1, c4 FROM tnf WHERE c1 = 2 AND c4 IS INFINITE;
SELECT c1, c4 FROM tnf WHERE c1 = 2 AND c4 IS NOT INFINITE;
SELECT c1, c4 FROM tnf WHERE c1 = 3 AND c4 IS NAN;  -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 3 AND c4 IS NOT NAN;  -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 3 AND c4 IS INFINITE;  -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 3 AND c4 IS NOT INFINITE;  -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 4 AND c4 IS NAN;
SELECT c1, c4 FROM tnf WHERE c1 = 4 AND c4 IS NOT NAN;
SELECT c1, c4 FROM tnf WHERE c1 = 4 AND c4 IS INFINITE;
SELECT c1, c4 FROM tnf WHERE c1 = 4 AND c4 IS NOT INFINITE;
SELECT c1, c4 FROM tnf WHERE c1 = 5 AND c4 IS NAN; -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 5 AND c4 IS NOT NAN; -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 5 AND c4 IS INFINITE; -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 5 AND c4 IS NOT INFINITE; -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 6 AND c4 IS NAN; -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 6 AND c4 IS NOT NAN; -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 6 AND c4 IS INFINITE; -- error: invalid number
SELECT c1, c4 FROM tnf WHERE c1 = 6 AND c4 IS NOT INFINITE; -- error: invalid number

SELECT c1, c5 FROM tnf WHERE c1 = 1 AND c5 IS NAN;
SELECT c1, c5 FROM tnf WHERE c1 = 1 AND c5 IS NOT NAN;
SELECT c1, c5 FROM tnf WHERE c1 = 1 AND c5 IS INFINITE;
SELECT c1, c5 FROM tnf WHERE c1 = 1 AND c5 IS NOT INFINITE;
SELECT c1, c5 FROM tnf WHERE c1 = 2 AND c5 IS NAN;
SELECT c1, c5 FROM tnf WHERE c1 = 2 AND c5 IS NOT NAN;
SELECT c1, c5 FROM tnf WHERE c1 = 2 AND c5 IS INFINITE;
SELECT c1, c5 FROM tnf WHERE c1 = 2 AND c5 IS NOT INFINITE;
SELECT c1, c5 FROM tnf WHERE c1 = 3 AND c5 IS NAN;  -- error: overflow
SELECT c1, c5 FROM tnf WHERE c1 = 3 AND c5 IS NOT NAN;  -- error: overflow
SELECT c1, c5 FROM tnf WHERE c1 = 3 AND c5 IS INFINITE;  -- error: overflow
SELECT c1, c5 FROM tnf WHERE c1 = 3 AND c5 IS NOT INFINITE;  -- error: overflow
SELECT c1, c5 FROM tnf WHERE c1 = 4 AND c5 IS NAN;
SELECT c1, c5 FROM tnf WHERE c1 = 4 AND c5 IS NOT NAN;
SELECT c1, c5 FROM tnf WHERE c1 = 4 AND c5 IS INFINITE;
SELECT c1, c5 FROM tnf WHERE c1 = 4 AND c5 IS NOT INFINITE;
SELECT c1, c5 FROM tnf WHERE c1 = 5 AND c5 IS NAN; -- error: invalid number
SELECT c1, c5 FROM tnf WHERE c1 = 5 AND c5 IS NOT NAN; -- error: invalid number
SELECT c1, c5 FROM tnf WHERE c1 = 5 AND c5 IS INFINITE; -- error: invalid number
SELECT c1, c5 FROM tnf WHERE c1 = 5 AND c5 IS NOT INFINITE; -- error: invalid number
SELECT c1, c5 FROM tnf WHERE c1 = 6 AND c5 IS NAN; -- error: invalid number
SELECT c1, c5 FROM tnf WHERE c1 = 6 AND c5 IS NOT NAN; -- error: invalid number
SELECT c1, c5 FROM tnf WHERE c1 = 6 AND c5 IS INFINITE; -- error: invalid number
SELECT c1, c5 FROM tnf WHERE c1 = 6 AND c5 IS NOT INFINITE; -- error: invalid number

-- all error: can't cast timestamp to double precision
SELECT c1, c6 FROM tnf WHERE c1 = 1 AND c6 IS NAN; 
SELECT c1, c6 FROM tnf WHERE c1 = 1 AND c6 IS NOT NAN;
SELECT c1, c6 FROM tnf WHERE c1 = 1 AND c6 IS INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 1 AND c6 IS NOT INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 2 AND c6 IS NAN;
SELECT c1, c6 FROM tnf WHERE c1 = 2 AND c6 IS NOT NAN;
SELECT c1, c6 FROM tnf WHERE c1 = 2 AND c6 IS INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 2 AND c6 IS NOT INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 3 AND c6 IS NAN; 
SELECT c1, c6 FROM tnf WHERE c1 = 3 AND c6 IS NOT NAN;
SELECT c1, c6 FROM tnf WHERE c1 = 3 AND c6 IS INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 3 AND c6 IS NOT INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 4 AND c6 IS NAN;
SELECT c1, c6 FROM tnf WHERE c1 = 4 AND c6 IS NOT NAN;
SELECT c1, c6 FROM tnf WHERE c1 = 4 AND c6 IS INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 4 AND c6 IS NOT INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 5 AND c6 IS NAN;
SELECT c1, c6 FROM tnf WHERE c1 = 5 AND c6 IS NOT NAN;
SELECT c1, c6 FROM tnf WHERE c1 = 5 AND c6 IS INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 5 AND c6 IS NOT INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 6 AND c6 IS NAN;
SELECT c1, c6 FROM tnf WHERE c1 = 6 AND c6 IS NOT NAN;
SELECT c1, c6 FROM tnf WHERE c1 = 6 AND c6 IS INFINITE;
SELECT c1, c6 FROM tnf WHERE c1 = 6 AND c6 IS NOT INFINITE;

SELECT c2 IS NAN, c2 IS NOT NAN, c2 IS INFINITE, c2 IS NOT INFINITE FROM tnf ORDER BY c1;
SELECT c3 IS NAN, c3 IS NOT NAN, c3 IS INFINITE, c3 IS NOT INFINITE FROM tnf ORDER BY c1;
SELECT c4 IS NAN, c4 IS NOT NAN, c4 IS INFINITE, c4 IS NOT INFINITE FROM tnf ORDER BY c1;
SELECT c5 IS NAN, c5 IS NOT NAN, c5 IS INFINITE, c5 IS NOT INFINITE FROM tnf ORDER BY c1;
SELECT c6 IS NAN, c6 IS NOT NAN, c6 IS INFINITE, c6 IS NOT INFINITE FROM tnf ORDER BY c1;

-- not
SELECT c1, c3, NOT(c3 IS NOT NAN), NOT(c3 IS NAN) FROM tnf ORDER BY c1;
SELECT c1, c3, NOT(c3 IS NOT INFINITE), NOT(c3 IS INFINITE) FROM tnf ORDER BY c1;

-- collation
SELECT c1, c2, c3, c4, c5 FROM tnf ORDER BY (c3 IS NAN);

SELECT (1 + 1) IS NAN;
SELECT (c3 + 1) IS NAN FROM tnf;
SELECT c1, c2, c3, c4, c5 FROM tnf WHERE (c3 + 1) IS NOT NAN;
SELECT c1, c2, c3, c4, c5 FROM tnf WHERE (c4 + 1) IS NOT NAN; -- error

SELECT (SELECT c3 FROM tnf WHERE c1 = 3) IS NAN;
SELECT c1, c2, c3, c4, c5 FROM tnf WHERE (SELECT c3 FROM tnf WHERE c1 = 5) IS nan;

SELECT c1, c2, c3, c4, c5 FROM tnf WHERE c1 IN (1, 2, 4, 5) AND c3 IS NAN;
SELECT c1, c2, c3, c4, c5 FROM tnf WHERE c1 IN (1, 2) AND c4 IS NAN;

set enable_expr_fusion=off;

-- parse view
CREATE VIEW v_t AS SELECT c3 IS NAN FROM tnf ORDER BY c1;
CREATE VIEW v_t2 AS SELECT c4 IS NOT NAN FROM tnf ORDER BY c1;
CREATE VIEW v_t3 AS SELECT c3 IS INFINITE FROM tnf ORDER BY c1;
CREATE VIEW v_t4 AS SELECT c4 IS NOT INFINITE FROM tnf ORDER BY c1;

\d+ v_t
\d+ v_t2
\d+ v_t3
\d+ v_t4

-- test where condition order
CREATE TABLE t_m (c1 int, c2 float8);
CREATE TABLE t_txt (c1 int, c2 text);

CREATE OR REPLACE FUNCTION batch_insert()
RETURNS int AS $$
DECLARE
	i INT;
	start INT;
    row_count INT := 2000;
BEGIN
	SELECT COUNT(*) INTO start FROM t_m;
    FOR i IN SELECT generate_series(1, row_count) LOOP
        INSERT INTO t_m VALUES (start + i, pg_catalog.random() * i);
    END LOOP;
	
	RETURN row_count;
END;
$$ LANGUAGE plpgsql;

SELECT batch_insert();
SELECT batch_insert();

UPDATE t_m set c2 = CAST('NAN' as float8) where c1 = 1000;
UPDATE t_m set c2 = CAST('INF' as float8) where c1 = 1001;
UPDATE t_m set c2 = NULL where c1 = 1002;

INSERT INTO t_txt SELECT c1, c2 FROM t_m;

-- without ANALYZE
SELECT * FROM t_m WHERE c2 IS NAN;
SELECT * FROM t_m WHERE c2 IS INFINITE;

SELECT * FROM t_txt WHERE c2 IS NAN; -- error
SELECT * FROM t_txt WHERE c2 IS INFINITE; -- error

-- test other condition combine with IS [NOT] NAN|INFINITE
SELECT * FROM t_txt WHERE c1 < 900 AND c2 IS NAN;
SELECT * FROM t_txt WHERE c1 < 900 AND c2 IS INFINITE;
SELECT * FROM t_txt WHERE c1 <= 900 AND c2 IS NAN;
SELECT * FROM t_txt WHERE c1 <= 900 AND c2 IS INFINITE;
SELECT * FROM t_txt WHERE c1 > 1010 AND c2 IS NAN;
SELECT * FROM t_txt WHERE c1 > 1010 AND c2 IS INFINITE;
SELECT * FROM t_txt WHERE c1 >= 1010 AND c2 IS NAN;
SELECT * FROM t_txt WHERE c1 >= 1010 AND c2 IS INFINITE;
SELECT * FROM t_txt WHERE c1 <> 1000 AND c1 <> 1001 AND c2 IS NAN;
SELECT * FROM t_txt WHERE c1 <> 1000 AND c1 <> 1001 AND c2 IS INFINITE;
SELECT * FROM t_txt WHERE c1 <> 1000 AND c2 IS NAN; -- error
SELECT * FROM t_txt WHERE c1 <> 1000 AND c2 IS INFINITE; -- error
SELECT * FROM t_txt WHERE c1 BETWEEN 500 AND 600 AND c2 IS NAN;
SELECT * FROM t_txt WHERE c1 BETWEEN 500 AND 600 AND c2 IS INFINITE;
SELECT * FROM t_txt WHERE c1 = 999 AND c2 IS NAN;
SELECT * FROM t_txt WHERE c1 = 999 AND c2 IS INFINITE;

SELECT * FROM t_txt WHERE c2 LIKE '12%' AND c2 IS NAN;
SELECT * FROM t_txt WHERE c2 LIKE '12%' AND c2 IS INFINITE;

SELECT * FROM t_txt WHERE c1 IN (10) AND C2 IS NAN; -- ok
SELECT * FROM t_txt WHERE c1 IN (10, 20) AND C2 IS NAN; -- ok
SELECT * FROM t_txt WHERE c1 IN (10, 20, 30) AND C2 IS NAN; -- error
SELECT * FROM t_txt WHERE c1 IN (10, 20, 30, 40, 60, 100, 120, 180) AND C2 IS NAN; -- error

-- with ANALYZE
ANALYZE t_m;
ANALYZE t_txt;

SELECT * FROM t_txt WHERE c1 < 900 AND c2 IS NAN;
SELECT * FROM t_txt WHERE c1 < 900 AND c2 IS INFINITE;
SELECT * FROM t_txt WHERE c1 <= 900 AND c2 IS NAN;
SELECT * FROM t_txt WHERE c1 <= 900 AND c2 IS INFINITE;
SELECT * FROM t_txt WHERE c1 > 1010 AND c2 IS NAN;
SELECT * FROM t_txt WHERE c1 > 1010 AND c2 IS INFINITE;
SELECT * FROM t_txt WHERE c1 >= 1010 AND c2 IS NAN;
SELECT * FROM t_txt WHERE c1 >= 1010 AND c2 IS INFINITE;
SELECT * FROM t_txt WHERE c1 <> 1000 AND c1 <> 1001 AND c2 IS NAN;
SELECT * FROM t_txt WHERE c1 <> 1000 AND c1 <> 1001 AND c2 IS INFINITE;
SELECT * FROM t_txt WHERE c1 <> 1000 AND c2 IS NAN; -- error
SELECT * FROM t_txt WHERE c1 <> 1000 AND c2 IS INFINITE; -- error
SELECT * FROM t_txt WHERE c1 BETWEEN 500 AND 600 AND c2 IS NAN;
SELECT * FROM t_txt WHERE c1 BETWEEN 500 AND 600 AND c2 IS INFINITE;
SELECT * FROM t_txt WHERE c1 = 999 AND c2 IS NAN;
SELECT * FROM t_txt WHERE c1 = 999 AND c2 IS INFINITE;

SELECT * FROM t_txt WHERE c2 LIKE '12%' AND c2 IS NAN;
SELECT * FROM t_txt WHERE c2 LIKE '12%' AND c2 IS INFINITE;

SELECT * FROM t_txt WHERE c1 IN (10) AND C2 IS NAN; -- ok
SELECT * FROM t_txt WHERE c1 IN (10, 20) AND C2 IS NAN; -- ok
SELECT * FROM t_txt WHERE c1 IN (10, 20, 30) AND C2 IS NAN; -- error
SELECT * FROM t_txt WHERE c1 IN (10, 20, 30, 40, 60, 100, 120, 180) AND C2 IS NAN; -- error

-- PBE
CREATE TABLE tnf2 (c1 int, func_name text, res boolean);

PREPARE isnan_text(int, text) AS INSERT INTO tnf2 VALUES 
($1 * 2 - 1, concat($2, ' IS NAN'), $2 IS NAN),
($1 * 2, concat($2, ' IS NOT NAN'), $2 IS NOT NAN);
EXECUTE isnan_text(1, '987654321');
EXECUTE isnan_text(2, '1.79E+100');
EXECUTE isnan_text(3, '9,223,372,036,854,775,807');
EXECUTE isnan_text(4, '1.79E+400');
EXECUTE isnan_text(5, '12.34yesterday');
EXECUTE isnan_text(6, 'tonight');
EXECUTE isnan_text(7, 'Nan');
EXECUTE isnan_text(8, 'Inf');
EXECUTE isnan_text(9, 'Infinite');

PREPARE isnan_num(int, float8) AS INSERT INTO tnf2 VALUES 
($1 * 2 - 1, concat($2, ' IS NAN'), $2 IS NAN),
($1 * 2, concat($2, ' IS NOT NAN'), $2 IS NOT NAN);
EXECUTE isnan_num(10, -9223372036854775808);
EXECUTE isnan_num(11, 1.23E-100);
EXECUTE isnan_num(12, -1.79E+100);
EXECUTE isnan_num(13, 1.79E+100);
EXECUTE isnan_num(14, 1.79E+400);
EXECUTE isnan_num(15, CAST('NaN' as float8));
EXECUTE isnan_num(16, CAST('Inf' as float8));

SELECT * FROM tnf2 ORDER BY c1;

drop view v_t;
drop view v_t2;
drop view v_t3;
drop view v_t4;
drop table tnf;
drop table tnf2;
drop table t_m;
drop table t_txt;
drop function batch_insert;

drop schema if exists expr_nan cascade;
